<?php
// Fetch Ladder //

if($is_ladder == 'Y')
{
	$ladderSql = "SELECT * FROM ".TABLEPREFIX."_game_ladder WHERE game_id = '$game_id' AND is_active='Y' ORDER BY ladder_order";
	$ladderArr = $UserManagerObjAjax->GetRecords("All",$ladderSql);
	$Numladder = count($ladderArr);
	for($l=0;$l<$Numladder;$l++)
	{
	  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
	  ug.ladder_id='".$ladderArr[$l]['ladder_id']."' AND ur.user_type_id=1 AND ur.user_id=u.user_id AND u.user_id=ug.user_id AND ug.user_type_id=1" ;
	  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
	  $ladderArr[$l]['cnt'] = $Arr['cnt'];
	} 
}	

//Fetch Race //
if($is_race == 'Y')
{
	$raceSql = "SELECT * FROM ".TABLEPREFIX."_game_race WHERE game_id = '$game_id' AND is_active='Y'";
	$raceArr = $UserManagerObjAjax->GetRecords("All",$raceSql);
	$Numrace = count($raceArr);
	for($r=0;$r<$Numrace;$r++)
	{
	  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
	  FIND_IN_SET(".$raceArr[$r]['race_id'].",ug.race_id) AND ur.user_type_id=1 AND ur.user_id=u.user_id AND u.user_id=ug.user_id AND ug.user_type_id=1";
	  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
	  $raceArr[$r]['cnt'] = $Arr['cnt'];
	} 
}	


// Fetch Server //
if($is_server == 'Y')
{
	$serverSql = "SELECT * FROM ".TABLEPREFIX."_game_server WHERE game_id = '$game_id' AND is_active='Y'";
	$serverArr = $UserManagerObjAjax->GetRecords("All",$serverSql);
    $Numserver = count($serverArr);
	for($s=0;$s<$Numserver;$s++)
	{
	  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
	  FIND_IN_SET(".$serverArr[$s]['server_id'].",ug.server_id) AND ur.user_type_id=1 AND ur.user_id=u.user_id AND u.user_id=ug.user_id AND ug.user_type_id=1";
	  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
	  $serverArr[$s]['cnt'] = $Arr['cnt'];
	} 

}	


// Fetch Region //
if($is_region == 'Y')
{
	$regionSql = "SELECT * FROM ".TABLEPREFIX."_game_region WHERE game_id = '$game_id' AND is_active='Y'";
	$regionArr = $UserManagerObjAjax->GetRecords("All",$regionSql);
	$Numregion = count($regionArr);
	for($a=0;$a<$Numregion;$a++)
	{
	  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
	  FIND_IN_SET(".$regionArr[$a]['region_id'].",ug.region_id) AND ur.user_type_id=1 AND ur.user_id=u.user_id AND u.user_id=ug.user_id AND ug.user_type_id=1";
	  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
	  $regionArr[$a]['cnt'] = $Arr['cnt'];
	} 
	
}
// Fetch rating //
if($is_rating == 'Y')
{
	$ratingSql = "SELECT * FROM ".TABLEPREFIX."_game_rating WHERE game_id = '$game_id' AND is_active='Y'";
	$ratingArr = $UserManagerObjAjax->GetRecords("All",$ratingSql);
	$Numrating = count($ratingArr);
	for($x=0;$x<$Numrating;$x++)
	{
	  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
	  FIND_IN_SET(".$ratingArr[$x]['rating_id'].",ug.rating_id) AND ur.user_type_id=1 AND ur.user_id=u.user_id AND u.user_id=ug.user_id AND ug.user_type_id=1";
	  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
	  $ratingArr[$x]['cnt'] = $Arr['cnt'];
	} 

}

// Availability //
$availSql = "SELECT count(u.user_id) FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
FIND_IN_SET('O',u.availability_type) AND ur.user_type_id=1 AND ur.user_id= u.user_id AND ug.user_id=u.user_id AND ug.game_id='$game_id' AND ug.user_type_id=1";
$avail1Arr = $UserManagerObjAjax->GetRecords("Row",$availSql);
$avail1Num = $avail1Arr[0];

$availSql = "SELECT count(u.user_id) FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE
FIND_IN_SET('L',u.availability_type) AND ur.user_type_id=1 AND ur.user_id = u.user_id AND ug.user_id=u.user_id AND ug.game_id='$game_id' AND ug.user_type_id=1";
$avail2Arr = $UserManagerObjAjax->GetRecords("Row",$availSql);
$avail2Num = $avail2Arr[0];

// Language //
 $languageSql = "SELECT lng.language_id AS language_id, lng.language_name AS language_name, count( lng.language_id ) AS cnt
FROM ".TABLEPREFIX."_language lng
JOIN ".TABLEPREFIX."_user st ON FIND_IN_SET( lng.language_id, st.language_ids )
AND lng.is_active = 'Y'
AND FIND_IN_SET( lng.language_id, (
SELECT u.language_ids
FROM ".TABLEPREFIX."_user u, ".TABLEPREFIX."_user_type_user_relation ur, ".TABLEPREFIX."_user_game game
WHERE u.is_active = 'Y'
AND ur.user_type_id =1 AND game.user_type_id=1
AND ur.user_id = u.user_id
AND u.user_id = st.user_id AND game.user_id = u.user_id AND game.game_id = '$game_id'
) )
GROUP BY lng.language_id, lng.language_name
ORDER BY lng.language_name ASC ";
$languageArr = $UserManagerObjAjax->GetRecords("All",$languageSql);
$Numlanguage = count($languageArr);

$availcoutSql = "SELECT country_id,country_name FROM ".TABLEPREFIX."_country ORDER BY country_name";
$AvailcounArr = $UserManagerObjAjax->HtmlOptionArrayCreate($availcoutSql);


$smarty->assign('Numladder',$Numladder);
$smarty->assign('ladderArr',$ladderArr);
$smarty->assign('Numrace',$Numrace);
$smarty->assign('raceArr',$raceArr);
$smarty->assign('Numserver',$Numserver);
$smarty->assign('serverArr',$serverArr);
$smarty->assign('Numregion',$Numregion);
$smarty->assign('regionArr',$regionArr);
$smarty->assign('Numrating',$Numrating);
$smarty->assign('ratingArr',$ratingArr);
$smarty->assign('AvailcounArr',$AvailcounArr);

$smarty->assign('is_ladder',$is_ladder);
$smarty->assign('is_server',$is_server);
$smarty->assign('is_race',$is_race);
$smarty->assign('is_region',$is_region);
$smarty->assign('is_rating',$is_rating);

$smarty->assign('avail1Num',$avail1Num);
$smarty->assign('avail2Num',$avail2Num);
$smarty->assign('Numlanguage',$Numlanguage);
$smarty->assign('languageArr',$languageArr);
$smarty->display('coach_leftpanel.tpl');   

?>